package com.apes.fn.business.insurance.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import org.springframework.stereotype.Component;

/**
 * @program: apes-server
 * @description e保养销售报表
 * @author: czs
 * @create: 2021-08-18 16:32
 **/
@Component
public class InsuranceDiscountsReportDao extends ApesDao {
    /**
     * 日结数据
     * @param param
     * @return
     */
    public JSONArray findEMaintain (JSONObject param) {
        String sql = "select name,SALESMAN,post,dept,operation,statistics_date,(date_price-REFUND_PRICE)date_price,(date_orders-REFUND_order)date_orders,\n" +
                "       maintain_order,maintain_price,maintain_plus_order,maintain_plus_price,tire_order,tire_price,tire_plus_order,tire_plus_price,\n" +
                "       (moth_RECEIPT_price-moth_REFUND_price)moth_RECEIPT_price,(moth_RECEIPT_order-moth_REFUND_order)moth_RECEIPT_order,\n" +
                "       (last_moth_RECEIPT_price-last_moth_REFUND_price)last_moth_RECEIPT_price,(last_moth_RECEIPT_order-last_moth_REFUND_order)last_moth_RECEIPT_order,\n" +
                "             (last_year_RECEIPT_order-last_year_REFUND_order)last_year_RECEIPT_order,(last_year_RECEIPT_price-last_year_REFUND_price)last_year_RECEIPT_price\n" +
                " from (select name,post,dept,operation,statistics_date,SALESMAN,NVL(date_price,0)date_price,NVL(date_orders,0)date_orders,NVL(maintain_order,0)maintain_order,NVL(maintain_price,0)maintain_price,NVL(maintain_plus_order,0)maintain_plus_order,NVL(maintain_plus_price,0)maintain_plus_price,\n" +
                "      (select NVL(sum(d.price),0) from V_INSURANCE_DISCOUNTS d where d.SALESMAN = v.SALESMAN and d.DEPT = v.dept and trunc(d.RECEIPT_DATE,'MM') = v.moth_date ) moth_RECEIPT_price," +
                "       NVL(tire_order,0)tire_order,NVL(tire_price,0)tire_price,NVL(tire_plus_order,0)tire_plus_order,NVL(tire_plus_price,0)tire_plus_price,\n" +

                "       (select NVL(count(*),0) from V_INSURANCE_DISCOUNTS d where d.SALESMAN = v.SALESMAN and d.DEPT = v.dept and trunc(d.RECEIPT_DATE,'MM') = v.moth_date )moth_RECEIPT_order,\n" +
                "       (select NVL(sum(d.price),0) from V_INSURANCE_DISCOUNTS d where d.SALESMAN = v.SALESMAN and d.DEPT = v.dept and trunc(d.REFUND_DATE,'MM') = v.moth_date ) moth_REFUND_price,\n" +
                "       (select NVL(count(*),0) from V_INSURANCE_DISCOUNTS d where d.SALESMAN = v.SALESMAN and d.DEPT = v.dept and trunc(d.REFUND_DATE,'MM') = v.moth_date )moth_REFUND_order,\n" +
                "       (select NVL(sum(d.price),0) from V_INSURANCE_DISCOUNTS d where d.SALESMAN = v.SALESMAN and d.DEPT = v.dept and trunc(d.RECEIPT_DATE,'MM') = trunc(moth_date-1,'MM')) last_moth_RECEIPT_price,\n" +
                "       (select NVL(count(*),0) from V_INSURANCE_DISCOUNTS d where d.SALESMAN = v.SALESMAN and d.DEPT = v.dept and trunc(d.RECEIPT_DATE,'MM') = trunc(moth_date-1,'MM'))last_moth_RECEIPT_order,\n" +
                "       (select NVL(sum(d.price),0) from V_INSURANCE_DISCOUNTS d where d.SALESMAN = v.SALESMAN and d.DEPT = v.dept and trunc(d.REFUND_DATE,'MM') = trunc(moth_date-1,'MM')) last_moth_REFUND_price,\n" +
                "       (select NVL(count(*),0) from V_INSURANCE_DISCOUNTS d where d.SALESMAN = v.SALESMAN and d.DEPT = v.dept and trunc(d.REFUND_DATE,'MM') = trunc(moth_date-1,'MM'))last_moth_REFUND_order,\n" +
                "        (select NVL(sum(d.price),0) from V_INSURANCE_DISCOUNTS d where d.SALESMAN = v.SALESMAN and d.DEPT = v.dept and d.REFUND_DATE = v.statistics_date ) REFUND_PRICE,\n" +
                "       (select NVL(count(d.price),0) from V_INSURANCE_DISCOUNTS d where d.SALESMAN = v.SALESMAN and d.DEPT = v.dept and d.REFUND_DATE = v.statistics_date ) REFUND_order,\n" +
                "        (select NVL(sum(d.price),0) from V_INSURANCE_DISCOUNTS d where d.SALESMAN = v.SALESMAN and d.DEPT = v.dept and d.RECEIPT_DATE = v.LAST_YEAR ) last_year_RECEIPT_price,\n" +
                "       (select NVL(count(d.price),0) from V_INSURANCE_DISCOUNTS d where d.SALESMAN = v.SALESMAN and d.DEPT = v.dept and d.RECEIPT_DATE = v.LAST_YEAR ) last_year_RECEIPT_order,\n" +
                "        (select NVL(sum(d.price),0) from V_INSURANCE_DISCOUNTS d where d.SALESMAN = v.SALESMAN and d.DEPT = v.dept and d.REFUND_DATE = v.LAST_YEAR ) last_year_REFUND_price,\n" +
                "       (select NVL(count(d.price),0) from V_INSURANCE_DISCOUNTS d where d.SALESMAN = v.SALESMAN and d.DEPT = v.dept and d.REFUND_DATE = v.LAST_YEAR ) last_year_REFUND_order\n" +
                "\n" +
                " from (select v.name,\n" +
                "       max(v.post)                                                                   post,\n" +
                "       v.dept                                                                   dept,\n" +
                "       max(v.operation)                                                              operation,\n" +
                "       max(v.SALESMAN)                                                              SALESMAN,\n" +
                "       v.RECEIPT_DATE                                                                statistics_date,\n" +
                "       sum(v.price)                                                                  date_price,\n" +
                "       count(v.name)                                                                 date_orders,\n" +
                "        trunc(RECEIPT_DATE,'MM') moth_date,\n" +
                "        TO_DATE(TO_CHAR(trunc(RECEIPT_DATE,'YYYY')-1,'YYYY-')||TO_CHAR(RECEIPT_DATE,'MM-DD'),'YYYY-MM-DD') LAST_YEAR,\n" +
                "       sum((case when v.E_MAINTAIN = '1' then 1  end)) maintain_order,\n" +
                "       sum((case when v.E_MAINTAIN in ('1') then v.price end))                  maintain_price,\n" +
                "       sum((case when v.E_MAINTAIN = '4' and v.price>0 then 1  end)) maintain_plus_order,\n" +
                "       sum((case when v.E_MAINTAIN in ('4') then v.price end))                  maintain_plus_price,\n" +
                "       sum((case  when v.E_MAINTAIN = '2' then 1 end)) tire_order,\n" +
                "       sum((case when v.E_MAINTAIN in ( '2') then v.price end))                  tire_price,\n" +
                "       sum((case when v.E_MAINTAIN = '3' and v.price>0 then 1 end)) tire_plus_order,\n" +
                "       sum((case when v.E_MAINTAIN in ('3') then v.price end))                  tire_plus_price\n" +
                " from (select (select p.NAME\n" +
                "              from MAS_PARTY_ROLE r,\n" +
                "                   MAS_PARTY p\n" +
                "              where v.SALESMAN = r.ID\n" +
                "                and p.ID = r.PARTY_ID)                           name,\n" +
                "             SALESMAN,\n" +
                "             (select e.ID\n" +
                "              from MAS_PARTY_ROLE r,\n" +
                "                   HR_EMPLOYEE e\n" +
                "              where v.SALESMAN = r.ID\n" +
                "                and e.PARTY_ID = r.PARTY_ID)                     post,\n" +
                "             dept,\n" +
                "             GET_YYMS(v.DEPT)                                    operation,\n" +
                "             RECEIPT_DATE,\n" +
                "             price,\n" +
                "             E_MAINTAIN\n" +
                "      from V_INSURANCE_DISCOUNTS v\n" +
                "    where RECEIPT_DATE=TRUNC(:start,'DD')\n" +
                "    OR REFUND_DATE=TRUNC(:end,'DD')) v\n" +
                " group by RECEIPT_DATE, name,dept)v)v";

        return toJSONArray(find(sql, param));
    }


}
